package dth.dao;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.AnnotationConfiguration;

import dth.entity.PaymentHistory;
import dth.entity.ReportMovie;
import dth.entity.ReportPackage;

public class ReportDAO {
	private SessionFactory sessionFactory=new AnnotationConfiguration().configure().buildSessionFactory();
	private Session session=sessionFactory.openSession();
	public List<ReportPackage> getListReportPackge(){
		List<ReportPackage> list=new ArrayList<ReportPackage>();
		session.beginTransaction();
		String query = "select p.package_name,count(*) as sum_suggest,SUM(package_price) as sum_price from user_package pu,package p where p.package_id=pu.fk_package GROUP BY p.package_name";
		Iterator it=session.createSQLQuery(query).list().iterator();
		while(it.hasNext()){
			Object[] row = (Object[]) it.next();
			ReportPackage p=new ReportPackage(row[0].toString(),Integer.parseInt(row[1].toString()),Double.parseDouble(row[2].toString()));
			list.add(p);
		}
		return list;
	}
	public List<ReportMovie> getListReportMovie(){
		List<ReportMovie> list=new ArrayList<ReportMovie>();
		session.beginTransaction();
		String query = "select m.movie_name,COUNT(*) as c,SUM(m.movie_price) as num from movie m,book_movie bm,`user` u where m.movie_id=bm.fk_movie_id and u.customer_id=bm.fk_user_id GROUP BY m.movie_name";
		Iterator it=session.createSQLQuery(query).list().iterator();
		while(it.hasNext()){
			Object[] row = (Object[]) it.next();
			ReportMovie reportMovie=new ReportMovie();
			reportMovie.setMovieName(row[0].toString());
			reportMovie.setSumSuggested(Integer.parseInt(row[1].toString()));
			reportMovie.setSumPrice(Double.parseDouble(row[2].toString()));
			list.add(reportMovie);
		}
		return list;
	}
}
